package com.pcjavanet.collect.mes.dao.impl.jdbc.postgresql;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
//import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.util.TimeZone;

import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.pcjavanet.collect.mes.dao.AbstractDao;
import com.pcjavanet.collect.mes.dao.DiscreteDataDao;
import com.pcjavanet.collect.mes.to.ChartData;
import com.pcjavanet.collect.mes.to.DiscreteData;
import com.pcjavanet.collect.mes.to.ParetoData;
import com.pcjavanet.collect.mes.to.SpcParetoException;
import com.pcjavanet.collect.mes.to.SpcRelParetoDataException;
import com.pcjavanet.collect.mes.util.CommonServerUtil;
import com.pcjavanet.collect.mes.util.ConvertFormula;
import com.pcjavanet.collect.mes.util.DaoSqlLogAssistant;
import com.pcjavanet.collect.mes.util.SystemConstant;

/**
 * class DiscreteDataDaoImpl.
 * @version 4.0
 * @author jerry_xia
 * @date January 12,2012
 */
public class DiscreteDataDaoImpl extends AbstractDao implements DiscreteDataDao{
	protected static Logger logger = Logger.getLogger(DiscreteDataDaoImpl.class);
	
	private static final String SELECT_MAX_DATA_KEY_BY_MONITOR_ID_SQL = "select max(discrete_data_key) from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_discrete_data where monitor_key=?  and data_status=0";
	/**
	 * get max data id by monitor id.
	 * @param monitorId -int
	 * @return dataId - int
	 * @throws DataAccessException
	 */
	public int getMaxDataKeyByMonitorId( final int monitorId ) throws DataAccessException {
		final List<Integer> dataIdList  = new ArrayList<Integer>();
		jdbcTemplate.query(SELECT_MAX_DATA_KEY_BY_MONITOR_ID_SQL, 
		new PreparedStatementSetter() {
		    public void setValues(PreparedStatement ps)	throws SQLException {
				ps.setInt(1, monitorId);
			}
		},
		new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				dataIdList.add(rs.getInt(1));
			}
		});
		if ( dataIdList.size() > 0 ) 
			return dataIdList.get(0);
		else {
			logger.error( " getMaxDataKeyByMonitorId -> monitorId:"+ monitorId + " ;ResultSet has no record ;return value 0"		 );
			String params =""+ monitorId ;
			logger.error( DaoSqlLogAssistant.getAfterFillParameterSQL( SELECT_MAX_DATA_KEY_BY_MONITOR_ID_SQL ,params ) );
			return 0; 
		}
	}

	private static final String  ADD_DISCRETE_DATA_SQL= "insert into "+CommonServerUtil.getSpcSchemaPrefix()+"spc_discrete_data (monitor_key,inspected,defective,defects,input_user,data_user,a_datetime" +
										",create_time ,discrete_data_key,data_status)  VALUES(?,?,?,?,?,? , ? , ?,?,?)";
//	private static final String GET_DATA_KEY_BY_MONITOR_KEY_SQL = "select max(discrete_data_key) from spc_discrete_data where monitor_key=? " ;
	
	private  int getNextSeqValue ( ) throws DataAccessException {
		final List<Integer> keys = new ArrayList<Integer>();
		String sql = " SELECT nextval('"+CommonServerUtil.getSpcSchemaPrefix()+"seq_spc_id')";
		if ( SystemConstant.COMPATIBLE_MODEL )
			sql = " SELECT nextval('seq_spc_id')";
		try {
			 jdbcTemplate.query(
				sql,
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						keys.add( rs.getInt(1));
					}
				}
			);
		} catch (Exception e) {
			logger.error( e );
		}
		return keys.get(0);
	}
	/**
	* add discrete data.
	* @param discreteData -DiscreteData
	* @return key -int
	* @throws DataAccessException
	*/
	public int addDiscreteData(final DiscreteData discreteData) throws DataAccessException {
		int key  =getNextSeqValue();
		discreteData.setDataKey(key);
		/* Jack Mo 2013-12-20 change this */
		try {
		 jdbcTemplate.update(
					ADD_DISCRETE_DATA_SQL,
		         new PreparedStatementSetter(){
		            public void setValues(PreparedStatement ps)	throws SQLException {
		            		ps.setInt(1, discreteData.getMonitorId());
		            		ps.setInt(2, (int)discreteData.getInspected());
		            		ps.setInt(3, (int)discreteData.getDefective());
		            		ps.setInt(4, (int)discreteData.getDefects());
		            		ps.setString(5, discreteData.getInputUser());
		            		ps.setInt(6, discreteData.getDataUser());
		            		ps.setLong(7, discreteData.getaDataTime() );
		            		ps.setLong(8, discreteData.getCreateTime() );
		            		ps.setInt(9, discreteData.getDataKey() );
		            		ps.setInt(10, discreteData.getDataStatus());
					}
				}
		    );
		}catch (Exception e) {
			logger.error( Thread.currentThread().getName() + ";"+ e.toString() );
		}
		
		/*
		if(result>0){
			final List<Integer> dataKey = new ArrayList<Integer>();
			try {
				jdbcTemplate.query(
						GET_DATA_KEY_BY_MONITOR_KEY_SQL,
					new PreparedStatementSetter(){
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, discreteData.getMonitorId());	
						}
					},
					new RowCallbackHandler() {
						public void processRow(ResultSet rs) throws SQLException {
							dataKey.add(rs.getInt(1));
						}
					}
				);
			} catch (Exception e) {
				logger.error(  Thread.currentThread().getName() + ";"+e.toString() );
			}
			key = Integer.parseInt(dataKey.get(0).toString());
		}
		*/
		return key;
	}
	
	private static final String  ADD_CHART_DATA_SQL= "insert into "+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data (data_key,chart_type,chart_key)  VALUES(?,?,?)";
	/**
	* add chart data.
	* @param chartData -ChartData
	* @return result -int
	* @throws DataAccessException
	*/
	public int addChartData(final ChartData chartData) throws DataAccessException {
		int result  =getNextSeqValue();
		chartData.setChartId( result );
		try {
			 jdbcTemplate.update(
					ADD_CHART_DATA_SQL,
		         new PreparedStatementSetter(){
		            public void setValues(PreparedStatement ps)	throws SQLException {
		            		ps.setInt(1, chartData.getDataId());
							ps.setString(2, chartData.getChartType());
							ps.setInt(3, chartData.getChartId());
					}
				}
		    );
		}catch (Exception e) {
			logger.error(  Thread.currentThread().getName() + ";"+e.toString());
		}
		
//		int key =0;
//		if(result>0){
//			key = getChartKeyByDataKeyAndChartType(chartData.getDataId(), chartData.getChartType());
//		}
		return result;
	}
	
	private static final String SELECT_DISCRETE_DATAS_BY_MONITOR_ID_AND_CHART_TYPE_AND_LAST_DATA_ID_SQL =
			"select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_discrete_data a , "+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b " +
			"where a.discrete_data_key = b.data_key and a.data_status=0 and a.monitor_key=? and b.chart_type =? and b.data_key > ? " +
			"order by b.data_key asc ";
	/**
	 * get discrete datas by monitor id and chart type and last data id.
	 * @param monitorId -int
	 * @param chartType -String
	 * @param lastDataId -int
	 * @return List<DiscreteData> - discreteDataList
	 * @throws DataAccessException
	 */
	public List<DiscreteData> getDiscreteDatasByMonitorIdAndChartTypeAndLastDataId(final int monitorId, final String chartType, final int lastDataId) throws DataAccessException {
		final List<DiscreteData> discreteDataList  = new ArrayList<DiscreteData>();
		jdbcTemplate.query(SELECT_DISCRETE_DATAS_BY_MONITOR_ID_AND_CHART_TYPE_AND_LAST_DATA_ID_SQL, 
				new PreparedStatementSetter() {
				    public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, monitorId);
						ps.setString(2, chartType);
						ps.setInt(3, lastDataId);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						discreteDataList.add(initDiscreteData(rs));
					}
		});
		return discreteDataList;
	}
	
	private static final String SELECT_DISCRETE_DATAS_BY_MONITOR_ID_AND_CHART_TYPE_AND_LAST_DATA_ID_AND_NEXT_DATA_ID_SQL = "" +
	"select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_discrete_data a ,"+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b " +
	"where a.discrete_data_key = b.data_key and a.data_status=0 and a.monitor_key=? and b.chart_type =? " +
	"and b.data_key > ? and b.data_key<= ? " +
	"order by b.data_key asc ";
	/**
	 * get discrete datas by monitor id and chart type and last data id and next data id.
	 * @param monitorId -int
	 * @param chartType -String
	 * @param lastDataId -int
	 * @param nextDataId -int
	 * @return List<DiscreteData> - discreteDataList
	 * @throws DataAccessException
	 */
	public List<DiscreteData> getDiscreteDatasByMonitorIdAndChartTypeAndLastDataIdAndNextDataId(final int monitorId, final String chartType, final int lastDataId, final int nextDataId) throws DataAccessException {
		final List<DiscreteData> discreteDataList  = new ArrayList<DiscreteData>();
		jdbcTemplate.query(SELECT_DISCRETE_DATAS_BY_MONITOR_ID_AND_CHART_TYPE_AND_LAST_DATA_ID_AND_NEXT_DATA_ID_SQL, 
				new PreparedStatementSetter() {
				    public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, monitorId);
						ps.setString(2, chartType);
						ps.setInt(3, lastDataId);
						ps.setInt(4, nextDataId);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						discreteDataList.add(initDiscreteData(rs));
					}
		});
		return discreteDataList;
	}
	
	private static final String SELECT_DISCRETE_DATAS_BY_LAST_POINT_WITH_CURRENT_REVISION_SQL = "select * from (select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_discrete_data a, "+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b " +
	"where a.discrete_data_key = b.data_key and a.data_status=0 and a.monitor_key=?  " +
	"and b.chart_type =? and  b.data_key > ? order by b.data_key desc limit ?)  " +
	"aa order by aa.data_key asc ";
	/**
	* get discrete datas by latest points with current revision.
	* @param monitorId -int
	* @param chartType -String
	* @param lastDataId -int
	* @param latestPoints -int
	* @return List<DiscreteData> - discreteDataList
	* @throws DataAccessException
	*/
	public List<DiscreteData> getDiscreteDatasByLatestPointsWithCurrentRevision(final int monitorId, final String chartType, final int lastDataId, final int latestPoints) throws DataAccessException {
		final List<DiscreteData> discreteDataList  = new ArrayList<DiscreteData>();
		jdbcTemplate.query(SELECT_DISCRETE_DATAS_BY_LAST_POINT_WITH_CURRENT_REVISION_SQL, 
				new PreparedStatementSetter() {
				    public void setValues(PreparedStatement ps)	throws SQLException {
				    	ps.setInt(1, monitorId);
				    	ps.setString(2, chartType);
						ps.setInt(3, lastDataId);
						ps.setInt(4, latestPoints);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						discreteDataList.add(initDiscreteData(rs));
					}
		});
		return discreteDataList;
	}
	
	private static final String SELECT_CURRENT_PAGE_DISCRETE_DATAS_BY_CHART_TYPE_AND_CURRENT_REVISION_SQL = "select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_discrete_data a, "+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b " +
	"where a.discrete_data_key = b.data_key and a.data_status=0 and a.monitor_key=?  " +
	"and b.chart_type =? and  b.data_key > ? order by b.data_key asc limit ? offset ? " ;
	/**
	* get current page discrete datas by chart type and current revision.
	* @param monitorId -int
	* @param chartType -String
	* @param lastDataId -int
	* @param limit -int
	* @param start -int
	* @return List<DiscreteData> - discreteDataList
	* @throws DataAccessException
	*/
	public List<DiscreteData> getCurrentPageDiscreteDatasByChartTypeAndCurrentRevision(final int monitorId, final String chartType, final int lastDataId, final int limit , final int start) throws DataAccessException {
		final List<DiscreteData> discreteDataList  = new ArrayList<DiscreteData>();
		jdbcTemplate.query(SELECT_CURRENT_PAGE_DISCRETE_DATAS_BY_CHART_TYPE_AND_CURRENT_REVISION_SQL, 
				new PreparedStatementSetter() {
				    public void setValues(PreparedStatement ps)	throws SQLException {
				    	ps.setInt(1, monitorId);
				    	ps.setString(2, chartType);
						ps.setInt(3, lastDataId);
						ps.setInt(4, limit);
						ps.setInt(5, start);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						discreteDataList.add(initDiscreteData(rs));
					}
		});
		return discreteDataList;
	}	

	private static final String SELECT_CURRENT_PAGE_DISCRETE_DATAS_BY_CHART_TYPE_AND_REVISION_SQL = "select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_discrete_data a, "+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b " +
	"where a.discrete_data_key = b.data_key and a.data_status=0 and a.monitor_key=?  " +
	"and b.chart_type =? and  b.data_key > ? and  b.data_key <= ? order by b.data_key asc limit ? offset ? " ;
	/**
	* get current page discrete datas by chart type and revision.
	* @param monitorId -int
	* @param chartType -String
	* @param lastDataId -int
	* @param nextDataId -int
	* @param limit -int
	* @param start -int
	* @return List<DiscreteData> - discreteDataList
	* @throws DataAccessException
	*/
	public List<DiscreteData> getCurrentPageDiscreteDatasByChartTypeAndRevision(final int monitorId, final String chartType, final int lastDataId, final int nextDataId, final int limit , final int start) throws DataAccessException {
		final List<DiscreteData> discreteDataList  = new ArrayList<DiscreteData>();
		jdbcTemplate.query(SELECT_CURRENT_PAGE_DISCRETE_DATAS_BY_CHART_TYPE_AND_REVISION_SQL, 
				new PreparedStatementSetter() {
				    public void setValues(PreparedStatement ps)	throws SQLException {
				    	ps.setInt(1, monitorId);
				    	ps.setString(2, chartType);
						ps.setInt(3, lastDataId);
						ps.setInt(4, nextDataId);
						ps.setInt(5, limit);
						ps.setInt(6, start);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						discreteDataList.add(initDiscreteData(rs));
					}
		});
		return discreteDataList;
	}
	
	private static final String SELECT_COUNT_DISCRETE_DATAS_BY_CURRENT_REVISION_SQL = "select count(*) from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_discrete_data a, "+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b " +
	"where a.discrete_data_key = b.data_key and a.data_status=0 and a.monitor_key=?  " +
	"and b.chart_type =? and  b.data_key > ? " ;
	/**
	* get count discrete datas by chart type and revision.
	* @param monitorId -int
	* @param chartType -String
	* @param lastDataId -int
	* @return count -int 
	* @throws DataAccessException
	*/
	public int getCountDiscreteDatasByChartTypeAndCurrentRevision(final int monitorId, final String chartType, final int lastDataId) throws DataAccessException {
		final List<Integer> countDiscreteDataList  = new ArrayList<Integer>();
		jdbcTemplate.query(SELECT_COUNT_DISCRETE_DATAS_BY_CURRENT_REVISION_SQL, 
				new PreparedStatementSetter() {
				    public void setValues(PreparedStatement ps)	throws SQLException {
				    	ps.setInt(1, monitorId);
				    	ps.setString(2, chartType);
						ps.setInt(3, lastDataId);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						countDiscreteDataList.add(rs.getInt(1));
					}
		});
		if ( countDiscreteDataList.size() >  0 ) {
			return countDiscreteDataList.get(0);
		} else {
			logger.error( " getCountDiscreteDatasByChartTypeAndCurrentRevision -> monitorId:"+ monitorId + " chartType"+ chartType + " lastDataId:"+lastDataId+" ;ResultSet has no record ;return value 0"		 );
			String params =""+ monitorId + "#'"+chartType + "'#"+lastDataId ;
			logger.error( DaoSqlLogAssistant.getAfterFillParameterSQL( SELECT_COUNT_DISCRETE_DATAS_BY_CURRENT_REVISION_SQL ,params ) );
			return 0; 
		}
	}
			
	private static final String SELECT_COUNT_DISCRETE_DATAS_BY_REVISION_SQL = "select count(*) from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_discrete_data a, "+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b " +
	"where a.discrete_data_key = b.data_key and a.data_status=0 and a.monitor_key=?  " +
	"and b.chart_type =? and  b.data_key > ? and b.data_key<=? " ;
	/**
	* get count discrete datas by chart type and revision.
	* @param monitorId -int
	* @param chartType -String
	* @param lastDataId -int
	* @param nextDataId -int
	* @return count -int 
	* @throws DataAccessException
	*/
	public int getCountDiscreteDatasByChartTypeAndRevision(final int monitorId, final String chartType, final int lastDataId, final int nextDataId) throws DataAccessException {
		final List<Integer> countDiscreteDataList  = new ArrayList<Integer>();
		jdbcTemplate.query(SELECT_COUNT_DISCRETE_DATAS_BY_REVISION_SQL, 
				new PreparedStatementSetter() {
				    public void setValues(PreparedStatement ps)	throws SQLException {
				    	ps.setInt(1, monitorId);
				    	ps.setString(2, chartType);
						ps.setInt(3, lastDataId);
						ps.setInt(4, nextDataId);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						countDiscreteDataList.add(rs.getInt(1));
					}
		});
		if ( countDiscreteDataList .size() > 0 )
			return countDiscreteDataList.get(0);
		else  {
			logger.error( " getCountDiscreteDatasByChartTypeAndRevision -> monitorId:"+ monitorId + " chartType"+ chartType + " lastDataId:"+lastDataId+ " nextDataId:"+ nextDataId + " ;ResultSet has no record ;return value 0"		 );
			String params =""+ monitorId + "#'"+chartType + "'#"+lastDataId + "#"+nextDataId;
			logger.error( DaoSqlLogAssistant.getAfterFillParameterSQL( SELECT_COUNT_DISCRETE_DATAS_BY_CURRENT_REVISION_SQL ,params ) );
			return 0; 
		}
	}
	
	private static final String SELECT_DISCRETE_DATAS_BY_LAST_POINT_WITH_OLD_REVISION_SQL = "select * from (select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_discrete_data a, "+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b " +
	"where a.discrete_data_key = b.data_key and a.data_status=0 and a.monitor_key=?  " +
	"and b.chart_type =? and  b.data_key > ? and b.data_key<= ? " +
	" order by b.data_key desc limit ?)  " +
	"aa order by aa.data_key asc ";
	/**
	* get discrete datas by latest points with old revision.
	* @param monitorId -int
	* @param chartType -String
	* @param lastDataId -int
	* @param nextDataId -int
	* @param latestPoints -int
	* @return List<DiscreteData> - discreteDataList
	* @throws DataAccessException
	*/
	public List<DiscreteData> getDiscreteDatasByLatestPointsWithOldRevision(final int monitorId, final String chartType, final int lastDataId, final int nextDataId, final int latestPoints) throws DataAccessException {
		final List<DiscreteData> discreteDataList  = new ArrayList<DiscreteData>();
		jdbcTemplate.query(SELECT_DISCRETE_DATAS_BY_LAST_POINT_WITH_OLD_REVISION_SQL, 
				new PreparedStatementSetter() {
				    public void setValues(PreparedStatement ps)	throws SQLException {
				    	ps.setInt(1, monitorId);
				    	ps.setString(2, chartType);
						ps.setInt(3, lastDataId);
						ps.setInt(4, nextDataId);
						ps.setInt(5, latestPoints);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						discreteDataList.add(initDiscreteData(rs));
					}
		});
		return discreteDataList;
	}
	
	private static final String SELECT_DISCRETE_DATAS_BY_DATE_WITH_CURRENT_REVISION_SQL = "" +
	"select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_discrete_data a, "+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b " +
	"where a.discrete_data_key = b.data_key and a.data_status=0 and a.monitor_key=? and b.chart_type =? and a.create_time >=? and a.create_time <=? and b.data_key >? " +
	"order by b.data_key asc ";
	/**
	* get discrete datas by date with current revision.
	* @param monitorId -int
	* @param chartType -String
	* @param lastDataId -int
	* @param startDate -Date
	* @param endDate -Date
	* @return List<DiscreteData> - discreteDataList
	* @throws DataAccessException
	*/
	public List<DiscreteData> getDiscreteDatasByDateWithCurrentRevision(final int monitorId, final String chartType, final int lastDataId, 
			final long startDate, final long endDate) throws DataAccessException {
		final List<DiscreteData> discreteDataList  = new ArrayList<DiscreteData>();
		jdbcTemplate.query(SELECT_DISCRETE_DATAS_BY_DATE_WITH_CURRENT_REVISION_SQL, 
				new PreparedStatementSetter() {
				    public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, monitorId);
						ps.setString(2, chartType);
						ps.setLong(3,   startDate  );
						ps.setLong(4,  endDate );
						ps.setInt(5, lastDataId);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						discreteDataList.add(initDiscreteData(rs));
					}
		});
		return discreteDataList;
	}
	
	private static final String SELECT_DISCRETE_DATAS_BY_DATE_WITH_OLD_REVISION_SQL = "" +
	"select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_discrete_data a ,"+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b " +
	"where a.discrete_data_key = b.data_key and a.data_status=0 and a.monitor_key=? and b.chart_type =? and a.create_time >=? and a.create_time <=? " +
	" and b.data_key >?  and b.data_key<=? " +
	"order by b.data_key asc ";
	/**
	* get discrete datas by date with old revision.
	* @param monitorId -int
	* @param chartType -String
	* @param lastDataId -int
	* @param nextDataId -int
	* @param startDate -Date
	* @param endDate -Date
	* @return List<DiscreteData> - discreteDataList
	* @throws DataAccessException
	*/
	public List<DiscreteData> getDiscreteDatasByDateWithOldRevision(final int monitorId, final String chartType, final int lastDataId, 
			final int nextDataId, final long startDate, final long endDate) throws DataAccessException {
		final List<DiscreteData> discreteDataList  = new ArrayList<DiscreteData>();
		jdbcTemplate.query(SELECT_DISCRETE_DATAS_BY_DATE_WITH_OLD_REVISION_SQL, 
				new PreparedStatementSetter() {
				    public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, monitorId);
						ps.setString(2, chartType);
						ps.setLong(3,   startDate   );
						ps.setLong(4,   endDate );
						ps.setInt(5, lastDataId);
						ps.setInt(6, nextDataId);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						discreteDataList.add(initDiscreteData(rs));
					}
		});
		return discreteDataList;
	}

	private static final String SELECT_LAST_FIFTEEN_DISCRETE_DATAS_BY_CHART_TYPE_AND_MONITOR_ID_AND_LAST_DATA_ID_SQL = "" +
	"select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_discrete_data a ,"+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b " +
	"where a.discrete_data_key = b.data_key and a.data_status=0 and a.monitor_key=? and b.chart_type =? and b.data_key >? " +
	"order by b.data_key desc limit 15 ";
	/**
	 * get last fifteen discrete datas by monitor id and chart type and last data id.
	 * @param monitorId -int
	 * @param chartType -String
	 * @param lastDataId -int
	 * @return List<ContinuousData> - discreteDataList
	 * @throws DataAccessException
	 */
	public LinkedList<DiscreteData> getLastFifteenDiscreteDatasByChartTypeAndMonitorIdAndLastDataId(final int monitorId, final String chartType,
			final int lastDataId) throws DataAccessException {
		final LinkedList<DiscreteData> discreteDataList  = new LinkedList<DiscreteData>();
		jdbcTemplate.query(SELECT_LAST_FIFTEEN_DISCRETE_DATAS_BY_CHART_TYPE_AND_MONITOR_ID_AND_LAST_DATA_ID_SQL, 
				new PreparedStatementSetter() {
				    public void setValues(PreparedStatement ps)	throws SQLException {
				    	ps.setInt(1, monitorId);
						ps.setString(2, chartType);
						ps.setInt(3, lastDataId);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						discreteDataList.add(initDiscreteData(rs));
					}
		});
		return discreteDataList;
	}
	
	
	private static final String SELECT_FOURTEEN_DISCRETE_DATAS_BEFORE_DATA_KEY_SQL = "" +
	"select * from (select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_discrete_data a, "+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b " +
	"where a.discrete_data_key = b.data_key and a.data_status=0 and a.monitor_key=? and b.chart_type =? and b.data_key < ? and b.data_key > " +
	"(select last_data_key from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_monitor_detail where monitor_key=?  and chart_type =? and revision =? limit 1) " +
	"order by b.data_key desc limit 14) aa order by aa.data_key asc ";
	/**
	* get fourteen discrete datas before data key by monitor id and chart type and revision.
	* @param monitorId -int
	* @param chartType -String
	* @param revision -int
	* @param dataKey -int
	* @throws DataAccessException
	*/
	public List<DiscreteData> getFourteenDiscreteDatasBeforeDataKey(final int monitorId, final String chartType, final int revision,
		final int dataKey) throws DataAccessException {
		final List<DiscreteData> discreteDataList  = new ArrayList<DiscreteData>();
		jdbcTemplate.query(SELECT_FOURTEEN_DISCRETE_DATAS_BEFORE_DATA_KEY_SQL, 
			new PreparedStatementSetter() {
			    public void setValues(PreparedStatement ps)	throws SQLException {
					ps.setInt(1, monitorId);
					ps.setString(2, chartType);
					ps.setInt(3, dataKey);
					ps.setInt(4, monitorId);
					ps.setString(5, chartType);
					ps.setInt(6, revision);
				}
			},
			new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					discreteDataList.add(initDiscreteData(rs));
				}
	    });
	
		return discreteDataList;
	}
	
	private static final String SELECT_MAX_REVISION_DISCRETE_DATAS_BY_MONITOR_ID_AND_CHART_TYPE = "" +
	"select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_discrete_data a, "+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b " +
	"where a.discrete_data_key = b.data_key and a.data_status=0 and a.monitor_key=? and b.chart_type =? and b.data_key > " +
	"(select last_data_key from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_monitor_detail where monitor_key=?  and chart_type =? and revision = " +
	"	(select max(revision) from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_monitor_detail where monitor_key=? and chart_type=?) " +
	"limit 1) ";
//	") order by a.discrete_data_key asc ";
	/**
	* get max revision discrete datas by monitor id and chart type.
	* @param monitorId -int
	* @param chartType -String
	* @throws DataAccessException
	*/
	public List<DiscreteData> getMaxRevisionDiscreteDatasByMonitorIdAndChartType(final int monitorId, final String chartType) throws DataAccessException {
		final List<DiscreteData> discreteDataList  = new ArrayList<DiscreteData>();
		jdbcTemplate.query(SELECT_MAX_REVISION_DISCRETE_DATAS_BY_MONITOR_ID_AND_CHART_TYPE, 
			new PreparedStatementSetter() {
			    public void setValues(PreparedStatement ps)	throws SQLException {
					ps.setInt(1, monitorId);
					ps.setString(2, chartType);
					ps.setInt(3, monitorId);
					ps.setString(4, chartType);
					ps.setInt(5, monitorId);
					ps.setString(6, chartType);
				}
			},
			new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					discreteDataList.add(initDiscreteData(rs));
				}
	    });
	
		return discreteDataList;
	}
	
	private static final String SELECT_LAST_DISCRETE_DATA_BY_MONITOR_ID_SQL = 
			"select *,to_timestamp(a_datetime)  #REPLACE#  as time from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_discrete_data  where data_status=0 and monitor_key=? and data_user=? order by discrete_data_key desc limit 1 ";
	
	/*public static void main(String[] args) throws Exception{
		String ip = "172.26.28.182";
		String user = "postgres";
		String password ="dbms4Fna";
		Class.forName("org.postgresql.Driver");
		Connection connection = null;
		connection = DriverManager.getConnection("jdbc:postgresql://"+ip+":5432/spc", user,password );
		if (connection != null) {
			ResultSet rs = connection.createStatement().executeQuery(" select *, to_timestamp(a_datetime) AT TIME ZONE '08' as time from spc_activity where monitor_key=218490118 and chart_type='U' and a_datetime>1373272497 order by serial_number");
			rs.next();
			DiscreteData discreteData = new DiscreteData();
			ActivityData activityData = new ActivityData();
			activityData.setTime(rs.getTimestamp("time"));
//			discreteData.setLastGroupDate( rs.getTimestamp("time").toString().substring(0, 19) );
			System.out.println(discreteData );
			System.out.println( activityData.getTime().toString() );
		}
	}*/
	
	/**
	* get last discrete data by monitor id and data user .
	* @param monitorId -int
	* @param dataUser -int
	* @return discreteDataList -List<DiscreteData>
	* @throws DataAccessException
	*/
	public List<DiscreteData> getLastDiscreteDataByMonitorIdAndDataUser(final int monitorId, final int dataUser ,final String netuserId) throws DataAccessException {
		final List<DiscreteData> discreteDataList  = new ArrayList<DiscreteData>();
		final String timezoneNumber =  CommonServerUtil.getTimeZoneNumberStr( netuserId );
		String timezone = CommonServerUtil.getSfdcTimeZoneForSql( netuserId ,DiscreteDataDaoImpl.class.getName()+"_getLastDiscreteDataByMonitorIdAndDataUser");
		String sql = SELECT_LAST_DISCRETE_DATA_BY_MONITOR_ID_SQL.replace("#REPLACE#", timezone);
		jdbcTemplate.query(sql, 
			new PreparedStatementSetter() {
			    public void setValues(PreparedStatement ps)	throws SQLException {
					ps.setInt(1, monitorId);
					ps.setInt(2, dataUser);
				}
			},
			new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					if(rs.getInt("discrete_data_key")!=0){
						DiscreteData discreteData = new DiscreteData();
						discreteData.setDataKey(rs.getInt("discrete_data_key"));
						discreteData.setMonitorId(rs.getInt("monitor_key"));
						discreteData.setInspected(rs.getInt("inspected"));
						discreteData.setDefective(rs.getInt("defective"));
						discreteData.setDefects(rs.getInt("defects"));
						discreteData.setCreateDate(rs.getTimestamp("create_date"));
						discreteData.setDataStatus(rs.getInt("data_status"));
						discreteData.setInputUser(rs.getString("input_user"));
//						discreteData.setCreateDateStr( discreteData.getCreateDate().toString().substring(0, 19) );
						discreteData.setCreateTime( rs.getLong("create_time" ));
						discreteData.setCreateDateStr( ConvertFormula.formateMilsecondToLenStr( rs.getLong("create_time") ) );
						discreteData.setLastGroupDate( rs.getTimestamp("time").toString().substring(0, 19) +timezoneNumber);
						discreteData.setA_DateTime(rs.getLong("a_datetime"));
						discreteData.setaDataTime(rs.getLong("a_datetime"));
						discreteData.setaDataTimeStr( ConvertFormula.formateMilsecondToLenStr( (rs.getLong("a_datetime")*1000) ) );
						discreteDataList.add(discreteData);
					}
					
				}
	    });
	
		return discreteDataList;
	}
	
	private static final String SELECT_DISCRETE_DATA_BY_DATA_ID_SQL = "select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_discrete_data  where discrete_data_key=? ";
	/**
	* get discrete data by data id.
	* @param dataId -int
	* @return discreteData -DiscreteData
	* @throws DataAccessException
	*/
	public DiscreteData getDiscreteDataByDataKey(final int dataId) throws DataAccessException {
		final List<DiscreteData> discreteDataList  = new ArrayList<DiscreteData>();
		jdbcTemplate.query(SELECT_DISCRETE_DATA_BY_DATA_ID_SQL, 
			new PreparedStatementSetter() {
			    public void setValues(PreparedStatement ps)	throws SQLException {
					ps.setInt(1, dataId);
				}
			},
			new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					DiscreteData discreteData = new DiscreteData();
					discreteData.setDataKey(rs.getInt("discrete_data_key"));
					discreteData.setMonitorId(rs.getInt("monitor_key"));
					discreteData.setInspected(rs.getInt("inspected"));
					discreteData.setDefective(rs.getInt("defective"));
					discreteData.setDefects(rs.getInt("defects"));
					discreteData.setCreateDate(rs.getTimestamp("create_date"));
					discreteData.setDataStatus(rs.getInt("data_status"));
					discreteData.setInputUser(rs.getString("input_user"));
					discreteData.setCreateTime( rs.getLong("create_time") );
					discreteData.setA_DateTime(rs.getLong("a_datetime"));
					discreteData.setaDataTime(rs.getLong("a_datetime"));
//					discreteData.setCreateDateStr(discreteData.getCreateDate().toString().substring(0, 19));
					discreteData.setaDataTimeStr( ConvertFormula.formateMilsecondToLenStr( (rs.getLong("a_datetime")*1000) ) +  CommonServerUtil.getSPCServerTimezoneStr( ) );
					discreteData.setCreateDateStr(  ConvertFormula.formateMilsecondToLenStr( rs.getLong("create_time") )+ CommonServerUtil.getSPCServerTimezoneStr( )  );
					discreteData.setCreateTimeStr( ConvertFormula.formateMilsecondToLenStr( rs.getLong("create_time") )+ CommonServerUtil.getSPCServerTimezoneStr( )  );
					discreteDataList.add(discreteData);
				}
	    });
		if ( discreteDataList.size() > 0 ) {
			return discreteDataList.get(0);
		} else {
			logger.error( " getDiscreteDataByDataKey -> dataId:"+ dataId +" ;ResultSet has no record ;return value null "		 );
			String params =""+ dataId ;
			logger.error( DaoSqlLogAssistant.getAfterFillParameterSQL( SELECT_DISCRETE_DATA_BY_DATA_ID_SQL ,params ) );
			return null; 
		}
	}
	
	private static final String SELECT_CHART_KEY_BY_DATA_KEY_AND_CHART_TYPE_SQL = "select chart_key from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data  where data_key=? and chart_type=? ";
	/**
	* get chart key by data key and chart type.
	* @param dataKey -int
	* @param chartType -String
	* @return chartKey -int
	* @throws DataAccessException
	*/
	public int getChartKeyByDataKeyAndChartType(final int dataKey, final String chartType) throws DataAccessException {
		final List<Integer> chartKeyList  = new ArrayList<Integer>();
		jdbcTemplate.query(SELECT_CHART_KEY_BY_DATA_KEY_AND_CHART_TYPE_SQL, 
			new PreparedStatementSetter() {
			    public void setValues(PreparedStatement ps)	throws SQLException {
					ps.setInt(1, dataKey);
					ps.setString(2, chartType);
				}
			},
			new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					chartKeyList.add(rs.getInt("chart_key"));
					
				}
	    });
		if ( chartKeyList.size() > 0 )
			return chartKeyList.get(0);
		else {
			logger.error( " getChartKeyByDataKeyAndChartType -> dataKey:"+ dataKey + " chartType"+ chartType + " ;ResultSet has no record ;return value 0"		 );
			String params =""+ dataKey + "#'"+chartType + "'";
			logger.error( DaoSqlLogAssistant.getAfterFillParameterSQL( SELECT_CHART_KEY_BY_DATA_KEY_AND_CHART_TYPE_SQL ,params ) );
			return 0; 
		}
			
	}
	
	private static final String SELECT_DISCRETE_DATAS_BY_MONITOR_ID_AND_DATE_SQL = "select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_discrete_data where monitor_key=? and create_time >=? and create_time <=? and data_status=? and data_user=? order by discrete_data_key asc ";
	/**
	* get discrete datas by monitor id and date .
	* @param monitorId -int
	* @param startDate -Date
	* @param endDate -Date
	* @param dataStatus -int
	* @param dataUser -int
	* @return List<DiscreteData> - discreteDataList
	* @throws DataAccessException
	*/
	public List<DiscreteData> getDiscreteDatasByMonitorNameAndDate(final int monitorId, final long startDate, final long endDate, final int dataStatus, final int dataUser) throws DataAccessException {
		final List<DiscreteData> discreteDataList  = new ArrayList<DiscreteData>();
		jdbcTemplate.query(SELECT_DISCRETE_DATAS_BY_MONITOR_ID_AND_DATE_SQL, 
				new PreparedStatementSetter() {
				    public void setValues(PreparedStatement ps)	throws SQLException {
				    	ps.setInt(1, monitorId);
				    	ps.setLong(2,  startDate );
						ps.setLong(3,  endDate );
						ps.setInt(4, dataStatus);
						ps.setInt(5, dataUser);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						DiscreteData discreteData = new DiscreteData();
						discreteData.setA_DateTime(rs.getLong("a_datetime"));
						discreteData.setaDataTime(rs.getLong("a_datetime"));
						discreteData.setDataKey(rs.getInt("discrete_data_key"));
						discreteData.setMonitorId(rs.getInt("monitor_key"));
						discreteData.setInspected(rs.getInt("inspected"));
						discreteData.setDefective(rs.getInt("defective"));
						discreteData.setDefects(rs.getInt("defects"));
						discreteData.setCreateDate(rs.getTimestamp("create_date"));
						discreteData.setDataStatus(rs.getInt("data_status"));
						discreteData.setInputUser(rs.getString("input_user"));
//						discreteData.setCreateDateStr(discreteData.getCreateDate().toString().substring(0, 19));
						discreteData.setCreateTime( rs.getLong("create_time" ));
						discreteData.setCreateDateStr( ConvertFormula.formateMilsecondToLenStr( rs.getLong("create_time") ) );
						discreteData.setDataUser(rs.getInt("data_user"));
						discreteData.setCreateTime( rs.getLong("create_time"));
						discreteData.setCreateTimeStr( ConvertFormula.formateMilsecondToLenStr( rs.getLong("create_time")));
						discreteDataList.add(discreteData);
					}
		});
		
		return discreteDataList;
	}
	
	/**
	*  get current page discrete datas by monitor id and date .
	* @param start -int
	* @param limit -int
	* @param monitorId -int
	* @param startDate -Date
	* @param endDate -Date
	* @param dataStatus -int
	* @param dataUser -int
	* @return List<DiscreteData> - discreteDataList
	* @throws DataAccessException
	*/
	public List<DiscreteData> getCurrentPageDiscreteDatasByMonitorNameAndDate(int start,int limit,final int monitorId, final long startDate, final long endDate, final int dataStatus, final int dataUser) throws DataAccessException {
		final List<DiscreteData> discreteDataList  = new ArrayList<DiscreteData>();
		StringBuffer sqlBuf = new StringBuffer(SELECT_DISCRETE_DATAS_BY_MONITOR_ID_AND_DATE_SQL);
		sqlBuf.append(" limit ").append(limit).append(" offset ").append(start);
		jdbcTemplate.query(sqlBuf.toString(), 
				new PreparedStatementSetter() {
				    public void setValues(PreparedStatement ps)	throws SQLException {
				    	ps.setInt(1, monitorId);
				    	ps.setLong(2,  startDate );
						ps.setLong(3,  endDate );
						ps.setInt(4, dataStatus);
						ps.setInt(5, dataUser);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						DiscreteData discreteData = new DiscreteData();
						discreteData.setA_DateTime(rs.getLong("a_datetime"));
						discreteData.setaDataTime(rs.getLong("a_datetime"));
						discreteData.setDataKey(rs.getInt("discrete_data_key"));
						discreteData.setMonitorId(rs.getInt("monitor_key"));
						discreteData.setInspected(rs.getInt("inspected"));
						discreteData.setDefective(rs.getInt("defective"));
						discreteData.setDefects(rs.getInt("defects"));
						discreteData.setCreateDate(rs.getTimestamp("create_date"));
						discreteData.setDataStatus(rs.getInt("data_status"));
						discreteData.setInputUser(rs.getString("input_user"));
//						discreteData.setCreateDateStr(discreteData.getCreateDate().toString().substring(0, 19));
						discreteData.setCreateTime( rs.getLong("create_time" ));
						discreteData.setCreateDateStr( ConvertFormula.formateMilsecondToLenStr( rs.getLong("create_time") ) );
						discreteData.setDataUser(rs.getInt("data_user"));
						discreteData.setCreateTime( rs.getLong("create_time"));
						discreteData.setCreateTimeStr( ConvertFormula.formateMilsecondToLenStr( rs.getLong("create_time")));
						discreteDataList.add(discreteData);
					}
		});
		
		return discreteDataList;
	}
	
	private static final String  UPDATE_DISCRETE_DATA_SQL= " update "+CommonServerUtil.getSpcSchemaPrefix()+"spc_discrete_data set inspected=?,defective=? ,defects=?   where discrete_data_key=? ";
	/**
	* update discrete data .
	* @param discreteData -DiscreteData
	* @return result -int
	* @throws DataAccessException
	*/
	public int updateDiscreteData(final DiscreteData discreteData) throws DataAccessException {
		int result = 0;
		try {
			result =jdbcTemplate.update(
					UPDATE_DISCRETE_DATA_SQL,
		         new PreparedStatementSetter(){
		            public void setValues(PreparedStatement ps)	throws SQLException {
		            	ps.setInt(1, (int) discreteData.getInspected());
		            	ps.setInt(2, (int) discreteData.getDefective());
		            	ps.setInt(3, (int) discreteData.getDefects());
		            	ps.setInt(4, discreteData.getDataKey());
					}
				}
		    );
		}catch (Exception e) {
			logger.error(e);
		}
		return result;
	}

	private static final String  UPDATE_DISCRETE_DATA_STATUS_SQL= " update "+CommonServerUtil.getSpcSchemaPrefix()+"spc_discrete_data set data_status=? where discrete_data_key=? " ;
	/**
	* update data status .
	* @param dataId -int
	* @param dataStatus -int
	* @return result -int
	* @throws DataAccessException
	*/
	public int updateDataStatus(final int dataId,final int dataStatus) throws DataAccessException {
		int result = 0;
		try {
			result =jdbcTemplate.update(
					UPDATE_DISCRETE_DATA_STATUS_SQL,
		         new PreparedStatementSetter(){
		            public void setValues(PreparedStatement ps)	throws SQLException {
		              	ps.setInt(1, dataStatus);
							ps.setInt(2, dataId);
					}
				}
		    );
		}catch (Exception e) {
			logger.error( e );
		}
		return result;
	}
	
	private DiscreteData initDiscreteData(ResultSet rs) throws SQLException {
		DiscreteData discreteData = new DiscreteData();
		discreteData.setDataKey(rs.getInt("discrete_data_key"));
		discreteData.setMonitorId(rs.getInt("monitor_key"));
		discreteData.setInspected(rs.getInt("inspected"));
		discreteData.setDefective(rs.getInt("defective"));
		discreteData.setDefects(rs.getInt("defects"));
		discreteData.setCreateDate(rs.getTimestamp("create_date"));
		discreteData.setDataStatus(rs.getInt("data_status"));
		String ocap = rs.getString("ocap");
		if ( ocap.equals(DiscreteData.ABNORMAL_POINT_AND_NO_OCAP))
			ocap = "";
		discreteData.setOcap( ocap );
		discreteData.setSendEmailTimes(rs.getInt("send_email_times"));
		discreteData.setSendEmailDate(rs.getTimestamp("send_email_date").toString());
		discreteData.setInputUser(rs.getString("input_user"));
		discreteData.setCreateTime(rs.getLong("create_time"));
		discreteData.setA_DateTime(rs.getLong("a_datetime"));
		discreteData.setaDataTime(rs.getLong("a_datetime"));
		discreteData.setCreateTimeStr( ConvertFormula.formateMilsecondToLenStr( rs.getLong("create_time" ) ) +CommonServerUtil.getSPCServerTimezoneStr()  );
		discreteData.setCreateDateStr( ConvertFormula.formateMilsecondToLenStr( rs.getLong("create_time" ) )+CommonServerUtil.getSPCServerTimezoneStr() );
//		discreteData.setCreateDateStr(discreteData.getCreateDate().toString().substring(0, 19));
		return discreteData;
	}

	private static final String  ADD_PARET_DATA_SQL= "INSERT INTO  "+CommonServerUtil.getSpcSchemaPrefix()+"spc_pareto_data (monitor_key , serial_number , defectcode , description  ,\"a_DateTime\" , defectqty, data_status ,create_time)  VALUES"
			+ "(?,?,?,?,?,?,? ,?)";
	public int addParetoData(final ParetoData paretoData)  {
		int result = 0;
		try {
			result =jdbcTemplate.update(
				ADD_PARET_DATA_SQL,
		        new PreparedStatementSetter(){
					public void setValues(PreparedStatement ps)	throws SQLException {
	            		ps.setInt(1, paretoData.getMonitorkey());
						ps.setString(2, paretoData.getSerialNumber());
						ps.setString(3, paretoData.getDefectcode() );
						ps.setString(4, paretoData.getDescription() );
						ps.setInt(5, paretoData.getaDateTime() );
						ps.setInt(6, paretoData.getDefectqty() );
						ps.setInt(7, paretoData.getDataStatus());
						ps.setLong(8, paretoData.getCreateTime());
					}
				}
		    );
		}catch (Exception e) {
			logger.error(  Thread.currentThread().getName() + ";"+paretoData.toString() );
			logger.error( e );
		} 
		return  result ;
	}

	public int  addParetoDatas(final List<ParetoData> datas) throws DataAccessException {
		final List<Integer> results = new ArrayList<Integer>();
		try {
			jdbcTemplate.batchUpdate(
					ADD_PARET_DATA_SQL, new BatchPreparedStatementSetter() {
						public void setValues(PreparedStatement ps,int i) throws SQLException {
							ParetoData paretoData = (ParetoData) datas.get(i);
							ps.setInt(1, paretoData.getMonitorkey());
							ps.setString(2, paretoData.getSerialNumber());
							ps.setString(3, paretoData.getDefectcode() );
							ps.setString(4, paretoData.getDescription() );
							ps.setInt(5, paretoData.getaDateTime() );
							ps.setInt(6, paretoData.getDefectqty() );
							ps.setInt(7, paretoData.getDataStatus());
							ps.setLong(8, paretoData.getCreateTime());
						}
						public int getBatchSize() {
							results.add( datas.size() );
							return datas.size();
						}
			});
		}catch (Exception e){
			logger.error(  Thread.currentThread().getName() +datas.toString()  );
			logger.error( e );
		}
		if ( results.size() > 0 )
			return results.get(0); 
		else {
			logger.error( "Can't addParetoDatas datas:"+datas.toString());
			return 0 ;
		}
	}
 
	public List<ParetoData> getParetoDatas(final int monitorId ,int beginDate , int endDate ) {
		final List<ParetoData> datas = new ArrayList<ParetoData>();
		StringBuffer sqlBuf = new StringBuffer() ;
		sqlBuf.append(" SELECT SUM( defectqty ) AS defectcodenumber, defectcode ,description ");
		sqlBuf.append(" FROM "+CommonServerUtil.getSpcSchemaPrefix()+"spc_pareto_data ");
		sqlBuf.append(" WHERE monitor_key = ").append(monitorId);
		sqlBuf.append("   AND  \"a_DateTime\" >= ").append(beginDate );
		sqlBuf.append("   AND  \"a_DateTime\" <=").append(endDate );		
		sqlBuf.append(" GROUP BY defectcode ,description ");
		sqlBuf.append(" ORDER BY defectcodenumber DESC "); //from high to low
		sqlBuf.append(" LIMIT 9 ");
		jdbcTemplate.query(
				sqlBuf.toString(),
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						ParetoData data = new ParetoData();
						data.setMonitorkey( monitorId ) ; 
						data.setDefectcode( rs.getString( "defectcode" ));
						data.setDescription( rs.getString( "description" ) );
						data.setCountnumber( rs.getInt( "defectcodenumber" ));
						datas.add(data);
					}
		});
		
		final List<Integer> result = new ArrayList<Integer>();
		StringBuffer sqlBuf2 = new StringBuffer() ;
//		sqlBuf2.append(" SELECT COUNT( pareto_data_key ) AS defectcodenumber ");
		sqlBuf2.append(" SELECT SUM( defectqty ) AS defectcodenumber ");
		sqlBuf2.append(" FROM "+CommonServerUtil.getSpcSchemaPrefix()+"spc_pareto_data ");
		sqlBuf2.append(" WHERE monitor_key = ").append(monitorId);
		sqlBuf2.append("   AND  \"a_DateTime\" >= ").append(beginDate );
		sqlBuf2.append("   AND  \"a_DateTime\" <=").append(endDate );		
		jdbcTemplate.query(
				sqlBuf2.toString(),
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						result.add( rs.getInt( "defectcodenumber" ) );
					}
		});
		
		int allDefects = 0 ;
		if ( result.size() > 0 )
		  allDefects =  result.get(0) ;
		
		if ( datas.size() == 9 ){
			int totalDefect = 0 ; 
			for( int i= 0 ; i< datas.size() ; i++ ){
				totalDefect = totalDefect + datas.get(i).getCountnumber() ;
			}
			int tenDefect = allDefects - totalDefect; 
			if ( tenDefect > 0 ){
				ParetoData data = new ParetoData();
				data.setMonitorkey( monitorId ) ; 
				data.setDefectcode( "Other");
				data.setDescription( "Other" );
				data.setCountnumber( tenDefect );
				datas.add(data);
			}
			
		}
		double cal = 0.0d;
		for( int i = 0 ; i < datas.size() ; i++ ) {
			double d = (double)datas.get( i ).getCountnumber();
			cal = cal+d;
			String cumulative = ConvertFormula.formatPercent(cal/allDefects ,3).replace("%", ""); 
			String p = ConvertFormula.formatPercent(d/allDefects ,3).replace("%", ""); 
			datas.get(i).setPercent( p ) ;
			datas.get(i).setCumulative(cumulative ) ;
			if ( datas.get(i).getDescription() ==null ||  "".equals(datas.get(i).getDescription())){
				datas.get(i).setDescription( datas.get(i).getDefectcode() );
			}
		}
		return datas;
	}

	public List<ParetoData> getAllParetoSNByDefectCode(final int monitorId ,final int beginDate ,final int endDate , String defectcode, boolean common ,String netuserId) {
		final String timezoneNumber =  CommonServerUtil.getTimeZoneNumberStr( netuserId );
		final List<ParetoData> datas = new ArrayList<ParetoData>();
		StringBuffer sqlBuf = new StringBuffer() ;
		sqlBuf.append(" SELECT create_time, data_status , defectqty, pareto_data_key, defectcode ,description,serial_number ,\"a_DateTime\" , TO_CHAR(TO_TIMESTAMP(\"a_DateTime\") " + CommonServerUtil.getSfdcTimeZoneForSql( netuserId ,DiscreteDataDaoImpl.class.getName()+"_getAllParetoSNByDefectCode") + ",'YYYY-MM-DD HH24:MI:SS') as dispDateTime ");
		sqlBuf.append(" FROM "+CommonServerUtil.getSpcSchemaPrefix()+"spc_pareto_data ");
		sqlBuf.append(" WHERE monitor_key = ").append(monitorId);
		if ( common == false )
			sqlBuf.append(" AND data_status=0 ");
		StringBuffer bf = new StringBuffer();
		if ( defectcode != null && defectcode.equalsIgnoreCase("Other" )) {
			List<ParetoData> ds = getTop9ParetoDatas(  monitorId ,  beginDate ,   endDate);
			if ( ds != null )
				for( int i = 0 ; i < ds.size() ; i++ ){
					if ( i == ( ds.size() -1) )
						bf.append("'").append( ds.get(i).getDefectcode()).append("'");
					else 
						bf.append("'").append(ds.get(i).getDefectcode()).append("',");
				}
			sqlBuf.append("   AND  defectcode NOT IN (").append( bf.toString() ).append(")");	
		} else {
			sqlBuf.append("   AND  defectcode='").append( defectcode ).append("'");	
		}
		sqlBuf.append("   AND  \"a_DateTime\" >= ").append(beginDate);
		sqlBuf.append("   AND  \"a_DateTime\" <=").append(endDate);
		sqlBuf.append(" ORDER BY \"a_DateTime\" DESC , pareto_data_key  DESC "); 
		jdbcTemplate.query(
			sqlBuf.toString(),
			new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					ParetoData data = new ParetoData();
					data.setMonitorkey( monitorId ) ; 
					data.setDataStatus( rs.getInt("data_status") );
					data.setDefectcode( rs.getString( "defectcode" ));
					data.setDescription( rs.getString( "description" ) );
					data.setSerialNumber( rs.getString( "serial_number" ) );
					data.setDispADateTime( rs.getString("dispDateTime")+timezoneNumber );
					data.setaDateTime( rs.getInt("a_DateTime"));
					data.setDatekey( rs.getInt("pareto_data_key") );
					data.setDefectqty( rs.getInt("defectqty") );
					data.setCreateTime(rs.getLong("create_time"));
					data.setCreateTimeStr( ConvertFormula.formateMilsecondToLenStr(rs.getLong("create_time")) + CommonServerUtil.getSPCServerTimezoneStr( ) );
					datas.add(data);
				}
		});
		return datas;
	}
	
	public String changeSecondToFormatTime( int seconds , String netuserId ) {
/*		
		String sql= "SELECT TO_CHAR(TO_TIMESTAMP("+seconds+") " + CommonServerUtil.getTimeZone( netuserId, DiscreteDataDaoImpl.class.getName()+"_getParetoSNByTime" )+ ",'YYYY-MM-DD HH24:MI:SS') as dispDateTime ";
		final List<String> rtv = new ArrayList<String>();
		try {
			jdbcTemplate.query(
					sql,
					new RowCallbackHandler() {
						public void processRow(ResultSet rs) throws SQLException {
							rtv.add( rs.getString("dispDateTime") +timezoneNumber);
						}
			});
		}catch (Exception e ) {
			logger.error(e);
		}
		if ( rtv.size() == 0 ) 
			return  ConvertFormula.conveMillisToString(seconds*1000);
		else 
			return rtv.get(0);*/
		
		final String timezoneNumber =  CommonServerUtil.getTimeZoneNumberStr( netuserId );
		String targetTimezone =  CommonServerUtil.getSfdcTimeZone( netuserId, DiscreteDataDaoImpl.class.getName()+"_changeSecondToFormatTime" );
		long targetOffsetSeconds = TimeZone.getTimeZone( targetTimezone ).getRawOffset();
		long serverOffsetSeconds =   TimeZone.getDefault().getRawOffset();
		long milsecond = ( seconds *1000l)+targetOffsetSeconds-serverOffsetSeconds  ;
		return  ( ConvertFormula.formateMilsecondToLenStr( milsecond ) + timezoneNumber );
		
	}
	
	public static void main(String[] args){
		int seconds =1372312603;
		long targetOffsetSeconds = TimeZone.getTimeZone( "America/Chicago" ).getRawOffset();
		long serverOffsetSeconds =   TimeZone.getDefault().getRawOffset();
		long milsecond = ( seconds *1000l)+targetOffsetSeconds-serverOffsetSeconds  ;
		System.out.println( targetOffsetSeconds );
		System.out.println( serverOffsetSeconds );
		System.out.println( ConvertFormula.formateMilsecondToLenStr(milsecond));
	}
	public List<ParetoData> getParetoSNByTime(final int monitorId ,int beginDate ,int endDate ,int start, int limit ,String netuserId) {
		final List<ParetoData> datas = new ArrayList<ParetoData>(); 
		StringBuffer sqlBuf = new StringBuffer() ;
		sqlBuf.append(" SELECT create_time, data_status, defectqty, pareto_data_key, defectcode ,description,serial_number ,\"a_DateTime\" , TO_CHAR(TO_TIMESTAMP(\"a_DateTime\") "
		+ CommonServerUtil.getSfdcTimeZoneForSql( netuserId, DiscreteDataDaoImpl.class.getName()+"_getParetoSNByTime" )+ ",'YYYY-MM-DD HH24:MI:SS') as dispDateTime ");
		final String timezoneNumber =  CommonServerUtil.getTimeZoneNumberStr( netuserId );
		sqlBuf.append(" FROM "+CommonServerUtil.getSpcSchemaPrefix()+"spc_pareto_data ");
		sqlBuf.append(" WHERE monitor_key = ").append(monitorId);
//		sqlBuf.append("  AND data_status=0 ");
		sqlBuf.append("   AND  \"a_DateTime\" >= ").append(beginDate );
		sqlBuf.append("   AND  \"a_DateTime\" <=").append(endDate );
		sqlBuf.append(" ORDER BY \"a_DateTime\" DESC "); 
		sqlBuf.append(" LIMIT ").append( limit ) .append(" OFFSET ").append( start );
		jdbcTemplate.query(
				sqlBuf.toString(),
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						ParetoData data = new ParetoData();
						data.setMonitorkey( monitorId ) ; 
						data.setDefectcode( rs.getString( "defectcode" ));
						data.setDescription( rs.getString( "description" ) );
						data.setSerialNumber( rs.getString( "serial_number" ) );
						data.setDispADateTime( rs.getString("dispDateTime") + timezoneNumber);
						data.setaDateTime( rs.getInt("a_DateTime"));
						data.setDatekey( rs.getInt("pareto_data_key"));
						data.setDefectqty( rs.getInt("defectqty"));
						data.setDataStatus(rs.getInt("data_status"));
						data.setCreateTime(rs.getLong("create_time"));
						data.setCreateTimeStr( ConvertFormula.formateMilsecondToLenStr(rs.getLong("create_time")) + CommonServerUtil.getSPCServerTimezoneStr( ) );
						datas.add(data);
					}
				});
		return datas;
	}
	
	public List<ParetoData> getParetoSNByTime(final int monitorId ,int beginDate ,int endDate ,String netuserId) {
		final List<ParetoData> datas = new ArrayList<ParetoData>(); 
		StringBuffer sqlBuf = new StringBuffer() ;
		sqlBuf.append(" SELECT  create_time,  data_status,defectqty, pareto_data_key,defectcode , description,serial_number ,\"a_DateTime\" , TO_CHAR(TO_TIMESTAMP(\"a_DateTime\") "+
		CommonServerUtil.getSfdcTimeZoneForSql( netuserId, DiscreteDataDaoImpl.class.getName()+"_getParetoSNByTime" ) +",'YYYY-MM-DD HH24:MI:SS') as dispDateTime ");
		final String timezoneNumber =  CommonServerUtil.getTimeZoneNumberStr( netuserId );
		sqlBuf.append(" FROM "+CommonServerUtil.getSpcSchemaPrefix()+"spc_pareto_data ");
		sqlBuf.append(" WHERE monitor_key = ").append(monitorId);
		sqlBuf.append("  AND data_status=0 ");
		sqlBuf.append("   AND  \"a_DateTime\" >= ").append(beginDate );
		sqlBuf.append("   AND  \"a_DateTime\" <=").append(endDate );
		sqlBuf.append(" ORDER BY \"a_DateTime\" DESC "); 
		jdbcTemplate.query(
				sqlBuf.toString(),
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						ParetoData data = new ParetoData();
						data.setMonitorkey( monitorId ) ; 
						data.setDataStatus(rs.getInt("data_status"));
						data.setDefectcode( rs.getString( "defectcode" ));
						data.setDescription( rs.getString( "description" ) );
						data.setSerialNumber( rs.getString( "serial_number" ) );
						data.setDispADateTime( rs.getString("dispDateTime") +timezoneNumber);
						data.setaDateTime( rs.getInt("a_DateTime"));
						data.setDatekey( rs.getInt("pareto_data_key"));
						data.setDefectqty( rs.getInt("defectqty"));
						data.setCreateTime(rs.getLong("create_time"));
						data.setCreateTimeStr( ConvertFormula.formateMilsecondToLenStr(rs.getLong("create_time")) + CommonServerUtil.getSPCServerTimezoneStr( ) );
						datas.add(data);
					}
				});
		return datas;
	}
	
	public int getTotalParetoSNByTime(int monitorId, int beginDate, int endDate) {
		final List<Integer> datas = new ArrayList<Integer>(); 
		StringBuffer sqlBuf = new StringBuffer() ;
		sqlBuf.append(" SELECT  COUNT( pareto_data_key ) ");
		sqlBuf.append(" FROM "+CommonServerUtil.getSpcSchemaPrefix()+"spc_pareto_data ");
		sqlBuf.append(" WHERE monitor_key = ").append(monitorId);
		sqlBuf.append("   AND  \"a_DateTime\" >= ").append(beginDate );
		sqlBuf.append("   AND  \"a_DateTime\" <=").append(endDate );
		jdbcTemplate.query(
				sqlBuf.toString(),
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
	 
						datas.add(rs.getInt(1));
					}
				});
		if ( datas.size() > 0 ) 
			return datas.get(0);
		else  {
			logger.error( "Can't getTotalParetoSNByTime monitorId:"+monitorId + ";beginDate:"+beginDate +";endDate:"+endDate);
			return 0  ;
		}
	}
	
	public int getTotalParetoSNByDefectCode(int monitorId, int  beginDate, int endDate, String defectcode) {
		final List<Integer> result = new ArrayList<Integer>();
		StringBuffer sqlBuf2 = new StringBuffer() ;
		sqlBuf2.append(" SELECT COUNT( pareto_data_key ) AS defectcodenumber ");
		sqlBuf2.append(" FROM "+CommonServerUtil.getSpcSchemaPrefix()+"spc_pareto_data ");
		sqlBuf2.append(" WHERE monitor_key = ").append(monitorId);
//		sqlBuf2.append(" AND data_status=0 ");
		sqlBuf2.append("   AND  \"a_DateTime\" >= ").append(beginDate );
		sqlBuf2.append("   AND  \"a_DateTime\" <=").append(endDate );		
		StringBuffer bf = new StringBuffer();
		if ( defectcode != null && defectcode.equalsIgnoreCase("Other" )) {
			List<ParetoData> ds = getTop9ParetoDatas(  monitorId ,  beginDate ,   endDate);
			if ( ds != null )
				for( int i = 0 ; i < ds.size() ; i++ ){
					if ( i == ( ds.size() -1) )
						bf.append("'").append( ds.get(i).getDefectcode()).append("'");
					else 
						bf.append("'").append(ds.get(i).getDefectcode()).append("',");
				}
			sqlBuf2.append("   AND  defectcode NOT IN (").append( bf.toString() ).append(")");	
		} else {
			sqlBuf2.append("   AND  defectcode='").append( defectcode ).append("'");	
		}
		jdbcTemplate.query(
				sqlBuf2.toString(),
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						result.add( rs.getInt( "defectcodenumber" ) );
					}
		});
		if (  result.size() > 0  ) {
			return result.get(0);
		} else {
			logger.error("Can't getTotalParetoSNByDefectCode monitorId:"+monitorId+ ";beginDate:"+beginDate+ ";endDate:"+endDate+ ";defectcode:"+defectcode);
			return 0 ; 
		}
	}

	private  List<ParetoData> getTop9ParetoDatas(final int monitorId ,int beginDate , int endDate ) {
		final List<ParetoData> datas = new ArrayList<ParetoData>();
		StringBuffer sqlBuf = new StringBuffer() ;
		sqlBuf.append(" SELECT COUNT( defectcode ) AS defectcodenumber, defectcode ,description ");
		sqlBuf.append(" FROM "+CommonServerUtil.getSpcSchemaPrefix()+"spc_pareto_data ");
		sqlBuf.append(" WHERE monitor_key = ").append(monitorId);
		sqlBuf.append("   AND  \"a_DateTime\" >= ").append(beginDate );
		sqlBuf.append("   AND  \"a_DateTime\" <=").append(endDate );		
		sqlBuf.append(" GROUP BY defectcode ,description ");
		sqlBuf.append(" ORDER BY defectcodenumber DESC "); //from high to low
		sqlBuf.append(" LIMIT 9 ");
		jdbcTemplate.query(
				sqlBuf.toString(),
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						ParetoData data = new ParetoData();
						data.setMonitorkey( monitorId ) ; 
						data.setDefectcode( rs.getString( "defectcode" ));
						data.setDescription( rs.getString( "description" ) );
						data.setCountnumber( rs.getInt( "defectcodenumber" ));
						datas.add(data);
					}
		});
		return datas;
	}
	
	public List<ParetoData> getOnePageParetoSNByDefectCode(final int monitorId,int beginDate, int endDate, String defectcode, int start,	int limit ,String netuserId) {
		final List<ParetoData> datas = new ArrayList<ParetoData>();
		StringBuffer sqlBuf = new StringBuffer() ;
		sqlBuf.append(" SELECT create_time, data_status,defectqty, pareto_data_key,defectcode , defectcode ,description,serial_number ,\"a_DateTime\" , TO_CHAR(TO_TIMESTAMP(\"a_DateTime\") "+
		CommonServerUtil.getSfdcTimeZoneForSql( netuserId , DiscreteDataDaoImpl.class.getName()+"_getOnePageParetoSNByDefectCode") +",'YYYY-MM-DD HH24:MI:SS') as dispDateTime ");
		final String timezoneNumber =  CommonServerUtil.getTimeZoneNumberStr( netuserId );
		sqlBuf.append(" FROM "+CommonServerUtil.getSpcSchemaPrefix()+"spc_pareto_data ");
		sqlBuf.append(" WHERE monitor_key = ").append(monitorId);
//		sqlBuf.append("   AND  data_status=0 ");
		sqlBuf.append("   AND  \"a_DateTime\" >= ").append(beginDate);
		sqlBuf.append("   AND  \"a_DateTime\" <=").append(endDate);
		StringBuffer bf = new StringBuffer();
		if ( defectcode != null && defectcode.equalsIgnoreCase("Other" )) {
			List<ParetoData> ds = getTop9ParetoDatas(  monitorId ,  beginDate ,   endDate);
			if ( ds != null )
				for( int i = 0 ; i < ds.size() ; i++ ){
					if ( i == ( ds.size() -1) )
						bf.append("'").append( ds.get(i).getDefectcode()).append("'");
					else 
						bf.append("'").append(ds.get(i).getDefectcode()).append("',");
				}
			sqlBuf.append("   AND  defectcode NOT IN (").append( bf.toString() ).append(")");	
		} else {
			sqlBuf.append("   AND  defectcode='").append( defectcode ).append("'");	
		}
		sqlBuf.append(" ORDER BY \"a_DateTime\" DESC "); 
		sqlBuf.append(" LIMIT ").append( limit ) .append(" OFFSET ").append( start );
//		logger.info( sqlBuf.toString());
		
		jdbcTemplate.query(
			sqlBuf.toString(),
			new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					ParetoData data = new ParetoData();
					data.setMonitorkey( monitorId ) ; 
					data.setDefectcode( rs.getString( "defectcode" ));
					data.setDescription( rs.getString( "description" ) );
					data.setSerialNumber( rs.getString( "serial_number" ) );
					data.setDispADateTime( rs.getString("dispDateTime")+timezoneNumber);
					data.setaDateTime( rs.getInt("a_DateTime"));
					data.setDatekey( rs.getInt("pareto_data_key"));
					data.setDefectqty( rs.getInt("defectqty"));
					data.setCreateTime(rs.getLong("create_time"));
					data.setCreateTimeStr( ConvertFormula.formateMilsecondToLenStr(rs.getLong("create_time")) + CommonServerUtil.getSPCServerTimezoneStr( ) );
					datas.add(data);
				}
		});
		return datas;
	}

	private static final String  ADD_REL_PARET_EXCEPTION_DATA_SQL= "INSERT INTO  "+CommonServerUtil.getSpcSchemaPrefix()+"spc_rel_pareto_data_exception (exception_key , pareto_data_key)  VALUES(?,?)";
	public int addSpcRelParetoDataException(final SpcRelParetoDataException spcRelParetoDataException ) {
		int result = 0;
		try {
			result =jdbcTemplate.update(
					ADD_REL_PARET_EXCEPTION_DATA_SQL,
		        new PreparedStatementSetter(){
					public void setValues(PreparedStatement ps)	throws SQLException {
	            		ps.setInt(1, spcRelParetoDataException.getExceptionKey() );
						ps.setInt(2, spcRelParetoDataException.getParetoDataKey() );
					}
				}
		    );
		}catch (Exception e) {
			logger.error( Thread.currentThread().getName() + ";"+ spcRelParetoDataException.toString() );
			logger.error( e );
		}
		return  result ;
	}
	
	public int addSpcRelParetoDataExceptions(final List<SpcRelParetoDataException> datas ) {
		final List<Integer> results = new ArrayList<Integer>();
		try {
			jdbcTemplate.batchUpdate(
					ADD_REL_PARET_EXCEPTION_DATA_SQL, new BatchPreparedStatementSetter() {
						public void setValues(PreparedStatement ps,int i) throws SQLException {
							SpcRelParetoDataException spcRelParetoDataException = (SpcRelParetoDataException) datas.get(i);
							ps.setInt(1, spcRelParetoDataException.getExceptionKey() );
							ps.setInt(2, spcRelParetoDataException.getParetoDataKey() );
						}
						public int getBatchSize() {
							results.add( datas.size() );
							return datas.size();
						}
			});
		}catch (Exception e){
			logger.error(  Thread.currentThread().getName() +datas.toString()  );
			logger.error( e );
		}
		if ( results.size()  > 0  )
			return results.get(0);
		else {
			logger.error( "Can't addSpcRelParetoDataExceptions datas:"+datas.toString());
			return 0 ; 
		}
		
	}
 
	private static final String  ADD_PARET_EXCEPTION_SQL= "INSERT INTO  "+CommonServerUtil.getSpcSchemaPrefix()+"spc_pareto_exception (pareto_exception_key , description,create_date,create_time)  VALUES(?,?,?,?)";
	public int addSpcParetoException(final SpcParetoException spcParetoException)  {
		int result = 0;
		try {
			result =jdbcTemplate.update(
					ADD_PARET_EXCEPTION_SQL,
		        new PreparedStatementSetter(){
					public void setValues(PreparedStatement ps)	throws SQLException {
	            		ps.setInt(1, spcParetoException.getParetoExceptionKey() );
						ps.setString(2, spcParetoException.getDescription() );
						ps.setTimestamp(3, new java.sql.Timestamp(  spcParetoException.getCreateDate().getTime() ) );
						ps.setLong(4, spcParetoException.getCreateTime() );
					}
				}
		    );
		}catch (RuntimeException e) {
			logger.error( Thread.currentThread().getName() + ";"+ spcParetoException.toString() );
			logger.error( e );
		}
		return  result ;
	}

	public List<ParetoData> getParetoExceptionData(final String  netuserId , final int monitorId,	final long startDate, final  long endDate ) {
		final List<ParetoData> datas = new ArrayList<ParetoData>();
		StringBuffer sqlBuf = new StringBuffer() ;
		String timezone = CommonServerUtil.getSfdcTimeZoneForSql( netuserId ,DiscreteDataDaoImpl.class.getName()+"_getParetoExceptionData");
		final String timezoneNumber =  CommonServerUtil.getTimeZoneNumberStr( netuserId );
//		sqlBuf.append(" SELECT   m.ocap, m.create_date, TO_CHAR( m.create_date ,'YYYY-MM-DD HH24:MI:SS') as dispDateTime, p.\"a_DateTime\"  as aDateTime,");
		sqlBuf.append(" SELECT p.create_time,  m.ocap, m.create_date, TO_CHAR( to_timestamp( p.\"a_DateTime\") "+ timezone + " , 'YYYY-MM-DD HH24:MI:SS') as dispDateTime, p.\"a_DateTime\"  as aDateTime,");
		sqlBuf.append("	 m.pareto_exception_key, p.defectcode,p.description as pdesc,m.description as mdesc");
		sqlBuf.append(" FROM "+CommonServerUtil.getSpcSchemaPrefix()+"spc_pareto_exception m, "+CommonServerUtil.getSpcSchemaPrefix()+"spc_pareto_data p ");
		sqlBuf.append(" WHERE p.pareto_data_key = m.pareto_exception_key " );
		sqlBuf.append("   AND p.monitor_key = ").append(monitorId);
		sqlBuf.append("   AND  m.create_time >= ? ");
		sqlBuf.append("   AND  m.create_time <=? ");
		sqlBuf.append(" ORDER BY m.create_date ASC  ,m.create_time"); //add m.create_time 
		try {
		jdbcTemplate.query(
				sqlBuf.toString(),
				new PreparedStatementSetter(){
		            public void setValues(PreparedStatement ps)	throws SQLException {
		            	ps.setLong(1,  startDate  );
		            	ps.setLong(2,    endDate );
		            }
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						ParetoData data = new ParetoData();
						data.setMonitorkey( monitorId ) ; 
						data.setDefectcode( rs.getString( "defectcode" ));
						data.setDescription( rs.getString( "pdesc" ) );
						data.setOcap( rs.getString( "ocap" ) );
						data.setExceptionDesc( rs.getString( "mdesc" ) );
						data.setDispADateTime( rs.getString("dispDateTime")+timezoneNumber);
						data.setCreateDate( (int) (rs.getTimestamp("create_date").getTime()/1000));
						data.setaDateTime( rs.getInt("aDateTime"));
						data.setDatekey( rs.getInt("pareto_exception_key"));
						data.setCreateTime(rs.getLong("create_time"));
						data.setCreateTimeStr( ConvertFormula.formateMilsecondToLenStr(rs.getLong("create_time")) + CommonServerUtil.getSPCServerTimezoneStr( ) );
						datas.add(data);
					}
			})	;
		} catch (Exception e) {
			logger.error(   Thread.currentThread().getName() + ";"+sqlBuf.toString() );
			logger.error(  e ) ;
		}
		return datas;
	}

	public SpcParetoException getSpcParetoExceptionByKey(final  int spcParetoExceptionKey ) {
		final List<SpcParetoException> datas = new ArrayList<SpcParetoException>();
		StringBuffer sqlBuf = new StringBuffer() ;
		sqlBuf.append("  SELECT *  FROM "+CommonServerUtil.getSpcSchemaPrefix()+"spc_pareto_exception  WHERE pareto_exception_key="+spcParetoExceptionKey );
		jdbcTemplate.query(
			sqlBuf.toString(),
			new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					SpcParetoException bean = new SpcParetoException();
					bean.setParetoExceptionKey(spcParetoExceptionKey);
					bean.setDescription(rs.getString("description"));
					bean.setOcap(rs.getString("ocap")); 
					bean.setStatus(rs.getInt("status"));
					bean.setCreateDate(rs.getTimestamp("create_date"));
					bean.setCreateTime(rs.getLong("create_time"));
					bean.setCreateTimeStr( ConvertFormula.formateMilsecondToLenStr(rs.getLong("create_time")) + CommonServerUtil.getSPCServerTimezoneStr( ) );
					datas.add( bean );
				}
			}
		);
		if ( datas.size() > 0)
			return datas.get(0);
		else {
			logger.error("Can't  getSpcParetoExceptionByKey spcParetoExceptionKey:"+spcParetoExceptionKey);
			return null; 
		}
	}

	public int updateSpcParetoException(final SpcParetoException spcParetoException) {
		int result = 0;
		String updateSql = " UPDATE  "+CommonServerUtil.getSpcSchemaPrefix()+"spc_pareto_exception SET status=? ,  description= ? ,ocap=?  WHERE pareto_exception_key=? ";
		try {
			result =jdbcTemplate.update(
					updateSql,
		         new PreparedStatementSetter(){
		            public void setValues(PreparedStatement ps)	throws SQLException {
		            	ps.setInt( 1,  spcParetoException.getStatus() );
		            	ps.setString( 2 ,  spcParetoException.getDescription() );
		            	ps.setString( 3 ,  spcParetoException.getOcap() );
		            	ps.setInt( 4 , spcParetoException.getParetoExceptionKey() );
					}
				}
		    );
		}catch (Exception e) {
			logger.error(e);
			logger.error(  Thread.currentThread().getName() + ";"+spcParetoException.toString() );
		}
		return result;
	}

	public int getTotalParetoSNByParetoDataKey( int dataKey ) {
		final List<Integer> datas = new ArrayList<Integer>(); 
		StringBuffer sqlBuf = new StringBuffer() ;
		sqlBuf.append("  SELECT COUNT(rel_key) FROM "+CommonServerUtil.getSpcSchemaPrefix()+"spc_rel_pareto_data_exception ");
		sqlBuf.append("  WHERE exception_key = ").append(dataKey);
		jdbcTemplate.query(
			sqlBuf.toString(),
			new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					datas.add(rs.getInt(1));
				}
			}
		);
		if ( datas.size() > 0)
			return datas.get(0);
		else {
			logger.error( "Can't getTotalParetoSNByParetoDataKey dataKey:"+dataKey);
			return 0;
		}
	}

	public List<ParetoData> getOnePageParetoSNByParetoDataKey(  int monitorId, int dataKey, int start, int limit ,String netuserId) {
		final List<ParetoData> datas = new ArrayList<ParetoData>();
		StringBuffer sqlBuf = new StringBuffer() ;
		sqlBuf.append(" SELECT create_time, defectqty,monitor_key, pareto_data_key, defectcode ,description,serial_number ,\"a_DateTime\" , TO_CHAR(TO_TIMESTAMP(\"a_DateTime\") " +
		CommonServerUtil.getSfdcTimeZoneForSql( netuserId , DiscreteDataDaoImpl.class.getName()+"_getOnePageParetoSNByParetoDataKey") +",'YYYY-MM-DD HH24:MI:SS')  as dispDateTime  FROM  "+CommonServerUtil.getSpcSchemaPrefix()+"spc_pareto_data ");
		final String timezoneNumber =  CommonServerUtil.getTimeZoneNumberStr( netuserId );
		sqlBuf.append("  WHERE pareto_data_key IN (  ");
		sqlBuf.append( 	" SELECT pareto_data_key FROM  "+CommonServerUtil.getSpcSchemaPrefix()+"spc_rel_pareto_data_exception WHERE exception_key=").append(dataKey).append(")");
		sqlBuf.append(" ORDER BY \"a_DateTime\" DESC "); 
		sqlBuf.append(" LIMIT ").append( limit ) .append(" OFFSET ").append( start );
		try {
		jdbcTemplate.query(
				sqlBuf.toString(),
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						ParetoData data = new ParetoData();
						data.setCreateTime(rs.getLong("create_time"));
						data.setCreateTimeStr( ConvertFormula.formateMilsecondToLenStr(rs.getLong("create_time")) + CommonServerUtil.getSPCServerTimezoneStr( ) );
						data.setMonitorkey( rs.getInt("monitor_key") ) ; 
						data.setDefectcode( rs.getString( "defectcode" ));
						data.setDescription( rs.getString( "description" ) );
						data.setSerialNumber( rs.getString( "serial_number" ) );
						data.setDispADateTime( rs.getString("dispDateTime")+timezoneNumber);
						data.setaDateTime( rs.getInt("a_DateTime"));
						data.setDatekey( rs.getInt("pareto_data_key"));
						data.setDefectqty( rs.getInt("defectqty"));
						datas.add(data);
					}
			})	;
		} catch (Exception e) {
			logger.error(   Thread.currentThread().getName() + ";"+sqlBuf.toString() );
			logger.error(  e ) ;
		}
		return datas;
	}
	
	public boolean deleteParetoRelativeDataByMonitorId( final int monitorId) {
			boolean rtv =true;
			//delete OCAP
			String sql =  " DELETE FROM  "+CommonServerUtil.getSpcSchemaPrefix()+"spc_ocap   WHERE  chart_key IN " +
					"( SELECT pareto_data_key FROM "+CommonServerUtil.getSpcSchemaPrefix()+"spc_pareto_data WHERE monitor_key=? )";
			try {
				jdbcTemplate.update(  
					sql,
					new PreparedStatementSetter(){
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1,monitorId   );
						}
					}
				);
				
			} catch(Exception e ) {
				rtv = false ;
				logger.error(e);
			}
			//delete spc_rel_pareto_data_exception
			if ( rtv ) {
				sql = " DELETE FROM  "+CommonServerUtil.getSpcSchemaPrefix()+"spc_rel_pareto_data_exception WHERE exception_key in ( " +
						" SELECT pareto_exception_key FROM "+CommonServerUtil.getSpcSchemaPrefix()+"spc_pareto_exception WHERE pareto_exception_key IN ( " +
						" SELECT pareto_data_key FROM "+CommonServerUtil.getSpcSchemaPrefix()+"spc_pareto_data WHERE monitor_key=? ) ) ";
				try {
					jdbcTemplate.update(  
						sql,
						new PreparedStatementSetter(){
							public void setValues(PreparedStatement ps)	throws SQLException {
								ps.setInt(1,monitorId   );
							}
						}
					);
					
				} catch(Exception e ) {
					rtv = false ;
					logger.error(e);
				}
			}
			//spc_pareto_exception
			if ( rtv ) {
				sql = " DELETE FROM  "+CommonServerUtil.getSpcSchemaPrefix()+"spc_pareto_exception WHERE pareto_exception_key IN ( " +
						" SELECT pareto_data_key FROM "+CommonServerUtil.getSpcSchemaPrefix()+"spc_pareto_data WHERE monitor_key=? )";
				try {
					jdbcTemplate.update(  
							sql,
							new PreparedStatementSetter(){
								public void setValues(PreparedStatement ps)	throws SQLException {
									ps.setInt(1,monitorId   );
								}
							}
							);
					
				} catch(Exception e ) {
					rtv = false ;
					logger.error(e);
				}
			}
			
			//delete pareto
			if ( rtv ) {
				sql = " DELETE FROM "+CommonServerUtil.getSpcSchemaPrefix()+"spc_pareto_data WHERE monitor_key=?";
				try {
					jdbcTemplate.update(  
							sql,
							new PreparedStatementSetter(){
								public void setValues(PreparedStatement ps)	throws SQLException {
									ps.setInt(1,monitorId   );
								}
							}
							);
					
				} catch(Exception e ) {
					rtv = false ;
					logger.error(e);
				}
			}
			//delete hold
			if ( rtv ) {
				sql = " DELETE FROM "+CommonServerUtil.getSpcSchemaPrefix()+"spc_hold WHERE monitor_key=?  ";
				try {
					jdbcTemplate.update(  
							sql,
							new PreparedStatementSetter(){
								public void setValues(PreparedStatement ps)	throws SQLException {
									ps.setInt(1,monitorId   );
								}
							}
							);
					
				} catch(Exception e ) {
					rtv = false ;
					logger.error(e);
				}
			}
			return rtv;
	}

	public List<ParetoData> getParetoDatasByCreateTime( final int monitorId , final  long createTime ) {
		final List<ParetoData> datas = new ArrayList<ParetoData>();
		String sql =  " SELECT  * FROM "+CommonServerUtil.getSpcSchemaPrefix()+"spc_pareto_data WHERE  monitor_key=?  AND create_time=?  ORDER BY \"a_DateTime\"  ASC " ; 
		jdbcTemplate.query(
			sql,
			new PreparedStatementSetter(){
				public void setValues(PreparedStatement ps)	throws SQLException {
					ps.setInt( 1 , monitorId );
					ps.setLong( 2 , createTime );
				}
			},
			new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					ParetoData data = new ParetoData();
					data.setMonitorkey( rs.getInt("monitor_key") ) ; 
					data.setDefectcode( rs.getString( "defectcode" ));
					data.setDescription( rs.getString( "description" ) );
					data.setSerialNumber( rs.getString( "serial_number" ) );
					data.setaDateTime( rs.getInt("a_DateTime"));
					data.setDatekey( rs.getInt("pareto_data_key"));
					data.setDefectqty( rs.getInt("defectqty"));
					datas.add(data);
				}
		});
		return datas;
	}
	
 
	final String SELECT_DISCRETE_DATAS_BY_MONITOR_ID_AND_HIDDEN_SQL= 
		" SELECT distinct discrete_data_key,create_date,defective,defects,inspected , a_datetime FROM  "+CommonServerUtil.getSpcSchemaPrefix()+"spc_discrete_data a ," +CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b "
				+ " WHERE a.data_status=-2 and monitor_key=? AND a.discrete_data_key=b.data_key AND ( chart_type='U' OR chart_type='P' OR chart_type='DPMO')  ORDER BY  discrete_data_key ASC";

	public List<DiscreteData> getNoDealByTimeData(final int monitorId) {
		final List<DiscreteData> discreteDataList = new ArrayList<DiscreteData>();
		jdbcTemplate.query(SELECT_DISCRETE_DATAS_BY_MONITOR_ID_AND_HIDDEN_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)
							throws SQLException {
						ps.setInt(1, monitorId);
					}
				}, new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						DiscreteData d = new DiscreteData();
						// d.setChartType(rs.getString("chart_type"));
						d.setDataKey(rs.getInt("discrete_data_key"));
						d.setCreateDate(rs.getTimestamp("create_date"));
						d.setDefective(rs.getInt("defective"));
						d.setDefects(rs.getInt("defects"));
						d.setInspected(rs.getInt("inspected"));
						d.setA_DateTime(rs.getLong("a_datetime"));
						d.setaDataTime(rs.getLong("a_datetime"));
						discreteDataList.add(d);
					}
				});
		return discreteDataList;
	}

	private static final String UPDATE_DISCRETE_HIDDEN_SQL = " update "+CommonServerUtil.getSpcSchemaPrefix()+"spc_discrete_data set data_status=0 ,create_date=? , create_time=? where discrete_data_key=? ";

	/**
	 * update discrete data .
	 * 
	 * @param discreteData
	 *            -DiscreteData
	 * @return result -int
	 * @throws DataAccessException
	 */
	public int updateDiscreteHiddenData(final DiscreteData discreteData)
			throws DataAccessException {
		int result = 0;
		try {
			result = jdbcTemplate.update(UPDATE_DISCRETE_HIDDEN_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)
								throws SQLException {
							ps.setTimestamp(1, new java.sql.Timestamp( discreteData.getCreateDate().getTime() ));
							ps.setLong(2, discreteData.getCreateDate().getTime());
							ps.setInt(3, discreteData.getDataKey());
						}
					});
		} catch (Exception e) {
			logger.error(e);
		}
		return result;
	}
	
	private static final String  FILL_A_DATE_TIME_SQL= " SELECT  a_datetime FROM  "+CommonServerUtil.getSpcSchemaPrefix()+"spc_activity  WHERE  spc_data_key=?  limit  1 ";
	 
	public void fillNoDealByTimeDataADateTime( final DiscreteData discreteData ) throws DataAccessException {
		try {
		jdbcTemplate.query(
				FILL_A_DATE_TIME_SQL,
				new PreparedStatementSetter(){
		            public void setValues(PreparedStatement ps)	throws SQLException {
		            	ps.setInt(1,  discreteData.getDataKey() );
		            }
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						discreteData.setA_DateTime(rs.getLong("a_datetime"));
					}
			})	;
		} catch (Exception e) {
			logger.error(  e ) ;
		}
	}
	

	final String SELECT_DISCRETE_DATAS_BY_MONITOR_ID_AND_HIDDEN_SQL2= 
		" SELECT distinct discrete_data_key,create_date,defective,defects,inspected , a_datetime FROM  "+CommonServerUtil.getSpcSchemaPrefix()+"spc_discrete_data a ," +CommonServerUtil.getSpcSchemaPrefix()+"spc_chart_data b "
				+ " WHERE a.data_status=-2 and monitor_key=? AND a.discrete_data_key=b.data_key AND   chart_type='FPY'    ORDER BY  discrete_data_key ASC";

	public List<DiscreteData> getNoDealByTimeData2(final int monitorId) {
		final List<DiscreteData> discreteDataList = new ArrayList<DiscreteData>();
		jdbcTemplate.query(SELECT_DISCRETE_DATAS_BY_MONITOR_ID_AND_HIDDEN_SQL2,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)
							throws SQLException {
						ps.setInt(1, monitorId);
					}
				}, new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						DiscreteData d = new DiscreteData();
						// d.setChartType(rs.getString("chart_type"));
						d.setDataKey(rs.getInt("discrete_data_key"));
						d.setCreateDate(rs.getTimestamp("create_date"));
						d.setDefective(rs.getInt("defective"));
						d.setDefects(rs.getInt("defects"));
						d.setInspected(rs.getInt("inspected"));
						d.setA_DateTime(rs.getLong("a_datetime"));
						d.setaDataTime(rs.getLong("a_datetime"));
						discreteDataList.add(d);
					}
				});
		return discreteDataList;
	}
	
	
	private static final String SELECT_LAST_DISCRETEDATA_BY_MONITOR_ID_SQL =
	"select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_discrete_data a  where  a.discrete_data_key = (select max(discrete_data_key) from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_discrete_data  b where b.monitor_key=? ) and	a.monitor_key=?";
	public  DiscreteData  getLastDiscreteDataByMonitorId(final int monitorId) throws DataAccessException {
		final List<DiscreteData> discreteDataList  = new ArrayList<DiscreteData>();
		jdbcTemplate.query(SELECT_LAST_DISCRETEDATA_BY_MONITOR_ID_SQL, 
				new PreparedStatementSetter() {
				    public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, monitorId);
						ps.setInt(2, monitorId);
					}
				},
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						DiscreteData discreteData = new DiscreteData();
						discreteData.setDataKey(rs.getInt("discrete_data_key"));
						discreteData.setMonitorId(rs.getInt("monitor_key"));
						discreteData.setCreateTime(rs.getLong("create_time"));
						discreteData.setA_DateTime(rs.getLong("a_datetime"));
						discreteData.setaDataTime(rs.getLong("a_datetime"));
						discreteDataList.add(discreteData  );
					}
		});
		
		if ( discreteDataList.size() > 0 ) 
			return discreteDataList.get(0);
		else {
			logger.error( "Can't getLastDiscreteDataByMonitorId monitorId:"+monitorId);
			return null;
		}
	}
	
}
